import argparse
import datetime
import os
today = datetime.datetime.now()
delta = datetime.timedelta(days=1)
yesterday = (today - delta).strftime("%Y-%m-%d")

parser = argparse.ArgumentParser()
parser.add_argument("--date", default=yesterday)
args = parser.parse_args()

sql = """
with tmp1 as(
select
*,
row_number() over(partition by device_id) rank
from dws.dws_member_start_day
where dt <= \"""" + args.date + """\" and dt >= date_sub(\"""" + args.date + """\", 6)
),
tmp2 as(
select
`device_id`,
`uid`,
`app_v`,
`os_type`,
`language`,
`channel`,
`area`,
`brand`,
dt,
date_sub(dt, rank) gid
from tmp1)
insert overwrite table dws.dws_member_start_consistent
partition(dt = \"""" + args.date + """\")
select
device_id,
concat_ws("|", collect_set(uid)) uid,
concat_ws("|", collect_set(app_v)) app_v,
concat_ws("|", collect_set(os_type)) os_type,
concat_ws("|", collect_set(language)) language,
concat_ws("|", collect_set(channel)) channel,
concat_ws("|", collect_set(area)) area,
concat_ws("|", collect_set(brand)) brand
from tmp2
group by device_id, gid
having count(1) >= 3
"""
os.system("hive -e \'" + sql + "\'")



